package co.th.bjc.md.helper;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import co.th.bjc.md.common.ConnectDB;
import co.th.bjc.md.model.Brand;

public class BrandHelper {
	
	private static Logger _log = Logger.getLogger(BrandHelper.class);
	
	public List<Brand> findBrandByCategory(BigDecimal category) throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Brand> listBrand = new ArrayList<Brand>(0);
		ConnectDB connectDB = null;
		try{
			String sql = "SELECT DISTINCT b.brand_code, b.brand_name_th, b.brand_name_en FROM brand b INNER JOIN product p ON b.brand_code = p.brand WHERE p.category = ?";
			_log.info("SQL : " + sql);
			
			connectDB = new ConnectDB();
			conn = connectDB.getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setBigDecimal(1, category);
			
			rs = stmt.executeQuery();
			while (rs.next()){
				listBrand.add(new Brand(rs.getBigDecimal("brand_code"), rs.getString("brand_name_th"), rs.getString("brand_name_en")));
			}
		}catch(Exception ex){
			_log.error("findBrandByCategory --> " + ex.getMessage());
			ex.printStackTrace();
		}finally{
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			connectDB.close();
		}
		return listBrand;
	}
	
	public List<Brand> findAllBrand() throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Brand> listBrand = new ArrayList<Brand>(0);
		ConnectDB connectDB = null;
		try{
			String sql = "SELECT * FROM brand ";
			_log.info("SQL : " + sql);
			
			connectDB = new ConnectDB();
			conn = connectDB.getConnection();
			stmt = conn.prepareStatement(sql);
			
			rs = stmt.executeQuery();
			while (rs.next()){
				listBrand.add(this.setBrand(rs));
			}
		}catch(Exception ex){
			_log.error("findAllBrand --> " + ex.toString());
			ex.printStackTrace();
		}finally{
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			connectDB.close();
		}
		return listBrand;
	}

	public Brand findBrandByBrandCode(BigDecimal brandCode) throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Brand brand = null;
		ConnectDB connectDB = null;
		try{
			String sql = "SELECT * FROM brand WHERE brand_code = ?";
			_log.info("SQL : " + sql);
			
			connectDB = new ConnectDB();
			conn = connectDB.getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setBigDecimal(1, brandCode);
			
			rs = stmt.executeQuery();
			if (rs.next()){
				brand = this.setBrand(rs);
			}
		}catch(Exception ex){
			_log.error("findBrandByBrandCode --> " + ex.toString());
			ex.printStackTrace();
		}finally{
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			connectDB.close();
		}
		return brand;
	}
	
	private Brand setBrand(ResultSet rs) throws Exception{
		Brand brand = new Brand();
		brand.setBrandCode(rs.getBigDecimal("brand_code"));
		brand.setBrandNameTh(rs.getString("brand_name_th"));
		brand.setBrandNameEn(rs.getString("brand_name_en"));
		brand.setCreateBy(rs.getString("create_by"));
		brand.setCreateDate(rs.getDate("create_date"));
		brand.setUpdateBy(rs.getString("update_by"));
		brand.setUpdateDate(rs.getDate("update_date"));
		return brand;
	}
}
